﻿CREATE TRIGGER Stoc_Comanda_Trigger
   ON  Stoc
   AFTER UPDATE
AS 
BEGIN
	DECLARE @epsilon float = 10
	DECLARE @stocCurent float = 0
	DECLARE @stocCritic float = 0
	DECLARE @id_Medicament int = NULL
	DECLARE @costCumparareMedicament float = 0
	DECLARE @zileLivrare int 
	DECLARE @currentDate datetime = GETDATE()
	DECLARE @dataVerificareStoc datetime
	
	DECLARE @table TABLE
	(
		Id_Medicament int,
		Checked bit DEFAULT(0)
	)
	
	INSERT INTO @table(Id_Medicament)
	SELECT inserted.Id_Medicament
	FROM inserted
	
	SELECT TOP 1 @id_Medicament = ID_Medicament FROM @table WHERE Checked = 0
	SELECT @costCumparareMedicament = CostCumparare FROM Medicament WHERE Id_Medicament = @id_Medicament
	
	WHILE @id_Medicament IS NOT NULL
	BEGIN
		UPDATE TOP (1) @table
		SET Checked = 1
		WHERE Id_Medicament = @id_Medicament AND Checked = 0
		
		SELECT @zileLivrare = p.Timp_Livrare
			FROM Medicament m
			INNER JOIN Producator p
			ON m.Id_Producator = p.Id_Producator
			WHERE m.Id_Medicament = @id_Medicament
		
		SET @dataVerificareStoc = dateadd(dd,@zileLivrare,@currentDate)
		
		SELECT @stocCurent = SUM(Cantitate_Actuala)
        FROM Stoc
		WHERE 
			Id_Medicament = @id_Medicament AND 
			Data_Intrare <= @dataVerificareStoc AND
			Data_Expirare > @dataVerificareStoc
		
		SELECT @stocCritic = Cantitate
		FROM Stoc_Critic 
		WHERE Id_Medicament = @id_Medicament
		
		IF @stocCurent - @epsilon <= @stocCritic
		BEGIN
			INSERT INTO Comanda(Id_Medicament,Cantitate,Cost,Data)
			VALUES (@id_Medicament,@stocCritic,@costCumparareMedicament,@currentDate)
			
			INSERT INTO Stoc(Id_Medicament,Data_Intrare,Data_Expirare,Cantitate_Actuala,Cantitate_Initiala)
			VALUES (@id_Medicament,DATEADD(dd,@zileLivrare,@currentDate),DATEADD(m,2,@currentDate),@stocCritic,@stocCritic)
		END
		
		SET @id_Medicament = NULL
		SELECT TOP 1 @id_Medicament = ID_Medicament FROM @table WHERE Checked = 0
	END
	
	
END
